#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
# Copyright (c) 2010 by Derek Anderson
#

import collections, datetime, json, numpy, os, sqlite3, sys, tempfile, textwrap, threading, time, traceback, weakref, uuid

import sqlparse


try:
  import pygtk
  pygtk.require("2.0")
  import gtk
  import gtk.glade
  import gtksourceview2
  import gobject, gnome
  import pango
except:
  traceback.print_exc()
  sys.exit(1)

try:
  import sexy
  USE_LIBSEXY=True
except:
  USE_LIBSEXY=False


from util import *


class Results(object):


  def __init__(self, main, conn):
    self.main = main
    self.conn = conn

    # setup execution notebook
    def drop_cb(notebook, context, x, y, time):
      src_notebook, page_num = get_parent_notebook_and_page( context.get_source_widget() )
      move_notebook_page_to_notebook( src_notebook, page_num, notebook )
      return True
    self.execute_notebook = gtk.Notebook()
    self.execute_notebook.set_tab_pos(gtk.POS_BOTTOM)
    self.execute_notebook.set_size_request(-1,64)
    #self.execute_notebook.connect('drag_drop', drop_cb)
    #self.execute_notebook.drag_dest_set(gtk.DEST_DEFAULT_ALL, DND_RESULTS, gtk.gdk.ACTION_MOVE)
    self.execute_no_results_bar = gtk.Alignment(xalign=0.5, yalign=0.5)
    self.execute_no_results_bar.add(gtk.Label('No results to display...'))
    self.execute_vbox = gtk.VBox()
    self.execute_vbox.pack_start(self.execute_notebook)
    self.execute_vbox.pack_start(self.execute_no_results_bar)
    
    
  def add_page_to_execute_notebook(self, page, label_text=None, label_markup=None):
    box = gtk.HBox()
    label = gtk.Label()
    if label_markup: label.set_markup(label_markup)
    else: label.set_text(label_text)
    close = gtk.Button()
    close.set_image( gtk.image_new_from_stock(gtk.STOCK_INDEX, gtk.ICON_SIZE_MENU) )
    close.set_relief(gtk.RELIEF_NONE)
    close.set_border_width(0)
    close.set_tooltip_text('click to keep tab for a while')
    close.connect('clicked',self.stick_and_close)
    event_box = gtk.EventBox()
    event_box.add(label)
    event_box.connect('button-press-event', self.handle_label_clicked, page)
    #event_box.drag_source_set(gtk.gdk.BUTTON1_MASK, DND_RESULTS, gtk.gdk.ACTION_MOVE)
    box.add(event_box)
    box.add(close)
    #event_box.connect("drag-failed", result_drag_failed_gen_window, page)
    box.show_all()
    page.show_all()
    self.execute_notebook.append_page(page, tab_label=box)
    self.execute_notebook.set_tab_reorderable(page,True)
    self.execute_notebook.set_current_page(-1)
    self.update_notebook_visible()
  
  
  def handle_label_clicked(self, label, event, page):
    if event.button==3:
      menu = gtk.Menu()

      # close submenu
      item = gtk.ImageMenuItem('Close')
      item.connect('activate', lambda *_: self.close_page(page) )
      menu.append(item)

      # close others submenu
      item = gtk.ImageMenuItem('Close others')
      item.connect('activate', lambda *_: self.close_other_pages(page) )
      item.set_sensitive(self.execute_notebook.get_n_pages() > 1)
      menu.append(item)

      # close all submenu
      item = gtk.ImageMenuItem('Close all')
      item.connect('activate', lambda *_: self.close_all_pages() )
      menu.append(item)
      
      # stick / unstick
      button = label.get_parent().get_children()[-1]
      stuck = button.get_image().get_stock()[0]==gtk.STOCK_CLOSE
      item = gtk.ImageMenuItem('Unstick' if stuck else 'Stick')
      def f(x, button):
        if button.get_image().get_stock()[0]==gtk.STOCK_INDEX:
          button.set_image( gtk.image_new_from_stock(gtk.STOCK_CLOSE, gtk.ICON_SIZE_MENU) )
        else:
          button.set_image( gtk.image_new_from_stock(gtk.STOCK_INDEX, gtk.ICON_SIZE_MENU) )
      item.connect('activate', f, button )
      menu.append(item)

      # close all submenu
      item = gtk.ImageMenuItem('Open in new window')
      def f(x,label):
        for i in range(self.execute_notebook.get_n_pages()):
          page = self.execute_notebook.get_nth_page(i)
          tab = self.execute_notebook.get_tab_label(page)
          lbl = tab.get_children()[0]
          print lbl, label
          if label == lbl:
            result_drag_failed_gen_window(label, None, self, page)
            self.update_notebook_visible()
            break
      item.connect('activate', f, label)
      menu.append(item)

      menu.show_all()
      menu.popup( None, None, None, event.button, event.time)
    
  
  def close_page(self, page):
    for i in range(self.execute_notebook.get_n_pages()):
      if page == self.execute_notebook.get_nth_page(i):
        self.execute_notebook.remove_page(i)
        break
    self.update_notebook_visible()
  
  
  def close_other_pages(self, page):
    for i in range(self.execute_notebook.get_n_pages()-1,-1,-1):
      if page != self.execute_notebook.get_nth_page(i):
        self.execute_notebook.remove_page(i)
    self.update_notebook_visible()
  
  
  def close_all_pages(self):
    while self.execute_notebook.get_n_pages():
      self.execute_notebook.remove_page(0)
    self.update_notebook_visible()
  
  
  def handle_cell_edited(self, cellrenderertext, path, new_text, treeview, column):
    print 'path', path
    print 'new_text', new_text
    print 'treeview', treeview
    print 'column', column
    sql = get_sql_associated_with_treeview(treeview)
    model = treeview.get_model()
    itr = model.get_iter(path)
    seen_tables = get_tables_from_sql(sql, schema=self.conn.schema)
    if len(seen_tables)!=1: return True
    col_index = treeview.get_columns().index(column)
    old_text = model.get_value(itr,col_index)
    #print 'old_text', old_text
    if old_text==new_text: return True
    column_names = [ x.get_title().replace('__','_') for x in treeview.get_columns() ]
    column_values = [model.get_value(itr,i) for i in range(len(column_names))]
    schema = seen_tables[0][0]
    table_name = seen_tables[0][1]
    column_info = self.conn.metadata[schema][table_name]
    if sum([column_info[x].get('primary_key',False) for x in column_names]):
      pked = [(x,y) for x,y in zip(column_names,column_values) if column_info[x].get('primary_key')]
      where_column_names, where_column_values = zip(*pked)
    else:
      where_column_names, where_column_values = column_names, column_values
    full_table_name = schema+self.conn.schema_tbl_sep+table_name if schema else table_name
    conditions = build_where_clause(where_column_names, where_column_values)
    update_sql = 'update %s set %s=%s where %s;' % (full_table_name, column_names[col_index], sql_param_quote(new_text), conditions)
    #print 'update_sql', update_sql
    buf = self.conn.query.get_buffer()
    buf.insert(buf.get_end_iter(),'\n\n'+update_sql)
    model.set_value(itr,col_index,new_text)
    return True
    

  def stick_and_close(self,button):
    if button.get_image().get_stock()[0]==gtk.STOCK_INDEX:
      button.set_image( gtk.image_new_from_stock(gtk.STOCK_CLOSE, gtk.ICON_SIZE_MENU) )
    else:
      for i in range(self.execute_notebook.get_n_pages()):
        child = self.execute_notebook.get_nth_page(i)
        label = self.execute_notebook.get_tab_label(child)
        if label.get_children()[1]==button:
          self.execute_notebook.remove_page(i)
          break
      self.update_notebook_visible()
  
  
  def update_notebook_visible(self):
    i = self.execute_notebook.get_n_pages()
    self.execute_notebook.set_visible(i)
    self.execute_no_results_bar.set_visible(not i)


  def clear_unsaved_tabs(self):
    i=0
    while i < self.execute_notebook.get_n_pages():
      child = self.execute_notebook.get_nth_page(i)
      label = self.execute_notebook.get_tab_label(child)
      stock_id = label.get_children()[1].get_image().get_stock()[0]
      if stock_id==gtk.STOCK_INDEX:
      #if not label.get_children()[1].get_active():
        self.execute_notebook.remove_page(i)
      else:
        i+=1
    self.update_notebook_visible()
  
    
  def create_query_results_page(self,sql,headers):

    treeview = gtk.TreeView()
    treeview.get_selection().set_mode(gtk.SELECTION_MULTIPLE)

    def gen_clipboard_text(a,b,treeview):
      print "b.keyval==ord('c') and b.state==gtk.gdk.CONTROL_MASK", b.keyval==ord('c'), b.state==gtk.gdk.CONTROL_MASK, b.state
      if b.keyval==ord('c'): # and b.state==gtk.gdk.CONTROL_MASK:
        s = treeview_to_csv(treeview)
        get_clipboard().set_text(s)
          
    treeview.connect('key-press-event', gen_clipboard_text, treeview)
    treeview.connect('button_press_event', self.results_treeview_button_press_event, sql)
    for i in range(len(headers)):
      col = headers[i]
      column = gtk.TreeViewColumn(col.replace('_','__'))
      column.set_resizable(True)
      treeview.append_column(column)
      cell = gtk.CellRendererText()
      cell.connect('edited',self.handle_cell_edited,treeview,column)
      cell.set_property('editable', True)
      column.pack_start(cell, True)
      column.add_attribute(cell, 'text', i)
      column.set_sort_column_id(i)
    treeview_scrollwindow = gtk.ScrolledWindow()
    treeview_scrollwindow.set_policy(gtk.POLICY_AUTOMATIC, gtk.POLICY_AUTOMATIC)
    treeview_scrollwindow.add(treeview)
    sql_label = gtk.Label()
    text = sql.replace('\n',' ')
    #sql_label.set_ellipsize(pango.ELLIPSIZE_MIDDLE)
    if len(text)>80: text = text[:77]+'...'
    text = '<span font_family="monospace">%s</span>' % pango_escape(text)
    sql_label.set_markup(text)
    #sql_label.set_size_request(480,-1)
    sql_expander = gtk.Expander()
    sql_expander.set_label_widget(sql_label)

    sql_textview = gtksourceview2.View()
    sql_textview.set_buffer( gtksourceview2.Buffer() )
    sql_textview.get_buffer().set_highlight_syntax(True)
    sql_textview.get_buffer().set_language( gtksourceview2.language_manager_get_default().get_language('sql') )
    sql_textview.modify_font(pango.FontDescription('monospace 8'))
    sql_textview.get_buffer().set_text(sql)
    sql_textview.set_editable(False)
#    scrolled_window = gtk.ScrolledWindow()
#    scrolled_window.set_policy(gtk.POLICY_AUTOMATIC, gtk.POLICY_AUTOMATIC)
#    scrolled_window.add(sql_textview)

    filter_hbox = gtk.HBox()

    sql_expander.add(sql_textview)
    page = gtk.VBox()
    page.pack_start(sql_expander, expand=False)
    page.pack_start(treeview_scrollwindow)
    page.pack_start(filter_hbox, expand=False)
    
    return page, treeview

  
  def results_treeview_button_press_event(self, treeview, event, sql):
    if event.button == 3:
      x = int(event.x)
      y = int(event.y)
      time = event.time
      menu = gtk.Menu()
      pthinfo = treeview.get_path_at_pos(x, y)
      seen_tables = get_tables_from_sql(sql, schema=self.conn.schema)

      # calculate submenu
      item = gtk.ImageMenuItem('Calculate')
      calculate_submenu = gtk.Menu()
      item.set_submenu(calculate_submenu)
      menu.append(item)
      
      # copy submenu
      item = gtk.ImageMenuItem('Copy')
      copy_submenu = gtk.Menu()
      item.set_submenu(copy_submenu)
      menu.append(item)
      
      # sql submenu
      item = gtk.ImageMenuItem('Create SQL')
      sql_submenu = gtk.Menu()
      item.set_submenu(sql_submenu)
      menu.append(item)
      
      # export submenu
      item = gtk.ImageMenuItem('Export')
      export_submenu = gtk.Menu()
      item.set_submenu(export_submenu)
      menu.append(item)
      
      if pthinfo is not None:
        path, col, cellx, celly = pthinfo
        column_names = [ x.get_title().replace('__','_') for x in treeview.get_columns() ]
        col_index = treeview.get_columns().index(col)
        treeview.grab_focus()
        col_name = col.get_title().replace('__','_')
        print 'col_name', col_name
        value = treeview.get_model().get_value(treeview.get_model().get_iter(path),col_index)
        print 'value', value
        if not treeview.get_selection().path_is_selected(path):
          treeview.set_cursor( path, col, 0)
        
        # hide column
        item = gtk.ImageMenuItem('Hide column')
        item.connect('activate', lambda x: col.set_visible(False) )
        menu.append(item)
        
        # filter
        item = gtk.ImageMenuItem('Filter')
        menu.append(item)
        filter_submenu = gtk.Menu()
        item.set_submenu(filter_submenu)
        def filter_callback(item, treeview, col_index, value, f):
          a_filter = treeview.get_model().filter_new()
          def a_callback(model, itr):
            try:
              return not f(float(model.get_value(itr,col_index)),float(value))
            except:
              return not f(model.get_value(itr,col_index),value)
          a_filter.set_visible_func(a_callback)
          treeview.set_model(a_filter)
          #a_filter.refilter()
          print 'woot', x
          
          filter_hbox = treeview.get_parent().get_parent().get_children()[2]
          abox = gtk.HBox()
          label = gtk.Label()
          label.set_markup(pango_escape(u'\u2212{' + menu_descape(item.get_children()[0].get_label())+'}', menu=False))
          close = gtk.Button()
          close.set_image( gtk.image_new_from_stock(gtk.STOCK_CLOSE, gtk.ICON_SIZE_MENU) )
          close.set_relief(gtk.RELIEF_NONE)
          close.set_border_width(0)
          close.set_tooltip_text('remove this and subsequent filters')
          def remove_filter(x, filter_hbox, abox, treeview):
            children = filter_hbox.get_children()
            for child in children[children.index(abox):]:
              filter_hbox.remove(child)
              treeview.set_model(treeview.get_model().get_model())
          close.connect('clicked',remove_filter, filter_hbox, abox, treeview)
          abox.pack_start(label, expand=False)
          abox.pack_start(close, expand=False)
          filter_hbox.pack_start(abox, expand=False)
          filter_hbox.show_all()
          
        item = gtk.ImageMenuItem(menu_escape('%s = %s' % (col_name,value)))
        filter_submenu.append(item)
        item.connect('activate', filter_callback, treeview, col_index, value, lambda a,b: a==b )
        item = gtk.ImageMenuItem(menu_escape(u'%s \u2260 %s' % (col_name,value)))
        filter_submenu.append(item)
        item.connect('activate', filter_callback, treeview, col_index, value, lambda a,b: a!=b )
        item = gtk.ImageMenuItem(menu_escape('%s < %s' % (col_name,value)))
        filter_submenu.append(item)
        item.connect('activate', filter_callback, treeview, col_index, value, lambda a,b: a<b )
        item = gtk.ImageMenuItem(menu_escape('%s > %s' % (col_name,value)))
        filter_submenu.append(item)
        item.connect('activate', filter_callback, treeview, col_index, value, lambda a,b: a>b )
        item = gtk.ImageMenuItem(menu_escape(u'%s \u2264 %s' % (col_name,value)))
        filter_submenu.append(item)
        item.connect('activate', filter_callback, treeview, col_index, value, lambda a,b: a<=b )
        item = gtk.ImageMenuItem(menu_escape(u'%s \u2265 %s' % (col_name,value)))
        filter_submenu.append(item)
        item.connect('activate', filter_callback, treeview, col_index, value, lambda a,b: a>=b )
        
        # FK lookups?
        #print 'self.relationships', self.relationships
        open_conn_ids = self.main.get_open_conn_ids()
        fk_rels = set([ v for k,v in self.conn.relationships.iteritems() if k[3]==col_name and v[0] in open_conn_ids ])
        print 'fk_rels', fk_rels
        if fk_rels:
          # lookup_id
          item = gtk.ImageMenuItem('Lookup in')
          menu.append(item)
          submenu = gtk.Menu()
          item.set_submenu(submenu)
          for v in fk_rels:
            item = gtk.MenuItem(menu_escape('%s%s%s.%s' % (v[1],self.conn.schema_tbl_sep,v[2],v[3])))
            item.connect('activate', self.main.lookup, v[0],v[1],v[2],v[3],value )
            submenu.append(item)
          # replace_with
          item = gtk.ImageMenuItem('Replace with')
          menu.append(item)
          submenu = gtk.Menu()
          item.set_submenu(submenu)
          def replace_col(x, treeview, col_index, conn_id, schema, table, col, replace_col):
            model = treeview.get_model()
            while isinstance(model,gtk.TreeModelFilter):
              model = model.get_model()
            values = set([row[col_index] for row in model])
            value_map = self.main.get_mapping(conn_id, schema, table, col, replace_col, values)
            for row in model:
              row[col_index] = value_map.get(row[col_index],u'\u2205')
            treeview.get_column(col_index).set_title(pango_escape(replace_col))
          for v in fk_rels:
            item = gtk.MenuItem(menu_escape('%s%s%s' % (v[1],self.conn.schema_tbl_sep,v[2])))
            #item.connect('activate', self.main.get_metadata, v[0],v[1],v[2] )
            submenu.append(item)
            subsubmenu = gtk.Menu()
            item.set_submenu(subsubmenu)
            for col in self.main.get_metadata(None,v[0],v[1],v[2]).iterkeys():
              if col==v[3]: continue
              item = gtk.MenuItem(menu_escape(col))
              item.connect('activate', replace_col, treeview, col_index, v[0], v[1], v[2], v[3], col )
              subsubmenu.append(item)
              
            

        # column stats
        try:
          col_values = numpy.array([float(row[col_index]) for row in treeview.get_model()])
          
          # sum
          item = gtk.ImageMenuItem('sum of column...')
          item.set_image(gtk.image_new_from_stock(gtk.STOCK_ADD, gtk.ICON_SIZE_MENU))
          def show_sum_column(x,col_values):
            total = col_values.sum()
            show_simple_info_dialog('Sum of column: <b>'+ repr(total) +'</b>')
          item.connect('activate', show_sum_column, col_values)
          calculate_submenu.append(item)
          
          # sum by
          item = gtk.ImageMenuItem('sum of column by')
          item.set_image(gtk.image_new_from_stock(gtk.STOCK_ADD, gtk.ICON_SIZE_MENU))
          sumby_submenu = gtk.Menu()
          item.set_submenu(sumby_submenu)
          calculate_submenu.append(item)
          def show_sum_column_by(x,model,by_column_name,column_name,by_index,col_index):
              counts = collections.defaultdict(float)
              for row in model:
                counts[row[by_index]] += float(row[col_index])
              print 'column_name', column_name
              tmp = [ (k,pango_escape(k+': '+repr(v))) for k,v in counts.iteritems() ]
              tmp.sort()
              show_simple_info_dialog('Sum of column <b>'+ pango_escape(column_name,menu=False) +'</b> by <b>'+ pango_escape(by_column_name,menu=False) +'</b>:\n\n' + '\n'.join([x[1] for x in tmp]))
          for i in range(len(column_names)):
            item = gtk.ImageMenuItem(pango_escape(column_names[i]))
            item.connect('activate', show_sum_column_by, treeview.get_model(), column_names[i], column_names[col_index], i, col_index)
            sumby_submenu.append(item)
          
          # absulute sum
          item = gtk.ImageMenuItem('absolute sum of column...')
          item.set_image(gtk.image_new_from_stock(gtk.STOCK_ADD, gtk.ICON_SIZE_MENU))
          def show_sum_column(x,col_values):
            total = numpy.abs(col_values).sum()
            show_simple_info_dialog('Absolute sum of column: <b>'+ repr(total) +'</b>')
          item.connect('activate', show_sum_column, col_values)
          calculate_submenu.append(item)
          
          # stats
          item = gtk.ImageMenuItem('stats of column...')
          item.set_image(gtk.image_new_from_stock(gtk.STOCK_ADD, gtk.ICON_SIZE_MENU))
          def show_stats_column(x,col_values):
            total = col_values.sum()
            mean = col_values.mean()
            std = col_values.std()
            s = [
              #'Sum of column: <b>'+ repr(total) +'</b>',
              'Mean of column: <b>'+ repr(mean) +'</b>',
              'Standard deviation of column: <b>'+ repr(std) +'</b>',
            ]
            show_simple_info_dialog('\n'.join(s))
          item.connect('activate', show_stats_column, col_values)
          calculate_submenu.append(item)
          
        except ValueError as e:
          print'note: column is non-numeric', e
          #traceback.print_exc()

        print 'path', path

      # rerun the query
      item = gtk.ImageMenuItem('Refresh')
      item.set_image(gtk.image_new_from_stock(gtk.STOCK_REFRESH, gtk.ICON_SIZE_MENU))
      def refresh_data(x,sql,treeview):
        def run():
          self.conn.execute(sql, treeview=treeview, log=False)
        t = threading.Thread(target=run)
        t.start()
      item.connect('activate', refresh_data, sql, treeview )
      menu.append(item)

      # copy data to clipboard
      def copy_data_to_clipboard(x,treeview):
        s = treeview_to_csv(treeview,only_selected=False,delim='\t')
        get_clipboard().set_text(s)
      item = gtk.ImageMenuItem('entire table')
      item.connect('activate', copy_data_to_clipboard, treeview )
      copy_submenu.append(item)

      # copy data to clipboard
      def copy_data_to_clipboard(x,treeview):
        s = treeview_to_csv(treeview,only_selected=True,delim='\t')
        get_clipboard().set_text(s)
      item = gtk.ImageMenuItem('selected rows')
      item.connect('activate', copy_data_to_clipboard, treeview )
      copy_submenu.append(item)

      # create insert statements
      def create_insert_statements(x,treeview,table_name):
        s = treeview_to_insert(treeview,table_name,only_selected=True)
        buf = self.conn.query.get_buffer()
        buf.insert(buf.get_end_iter(),'\n\n'+s)
      if len(seen_tables)==1:
        item = gtk.ImageMenuItem('insert statements')
        item.connect('activate', create_insert_statements, treeview, seen_tables[0][0]+self.conn.schema_tbl_sep+seen_tables[0][1] if seen_tables[0][0] else self.conn.schema_tbl_sep+seen_tables[0][1] )
        sql_submenu.append(item)

      # create update statements
      def create_update_statements(x,treeview,table_name):
        s = treeview_to_update(treeview,table_name,only_selected=True)
        buf = self.conn.query.get_buffer()
        buf.insert(buf.get_end_iter(),'\n\n'+s)
      if len(seen_tables)==1:
        item = gtk.ImageMenuItem('update statements')
        item.connect('activate', create_update_statements, treeview, seen_tables[0][0]+self.conn.schema_tbl_sep+seen_tables[0][1] if seen_tables[0][0] else self.conn.schema_tbl_sep+seen_tables[0][1] )
        sql_submenu.append(item)

      # create delete statements
      def create_delete_statements(x,treeview,table_name):
        s = treeview_to_delete(treeview,table_name,only_selected=True)
        buf = self.conn.query.get_buffer()
        buf.insert(buf.get_end_iter(),'\n\n'+s)
      if len(seen_tables)==1:
        item = gtk.ImageMenuItem('delete statements')
        item.connect('activate', create_delete_statements, treeview, seen_tables[0][0]+self.conn.schema_tbl_sep+seen_tables[0][1] if seen_tables[0][0] else self.conn.schema_tbl_sep+seen_tables[0][1] )
        sql_submenu.append(item)

      if pthinfo is not None:
        # copy column to clipboard
        def copy_column_to_clipboard(x,treeview,col_index):
          data = [str(row[col_index]) for row in treeview.get_model()]
          for i in range(len(data)):
            if '"' in data[i]:
              data[i] = '"%s"' % (data[i].replace('"','\"'))
          s = ','.join(data)
          get_clipboard().set_text(s)
        item = gtk.ImageMenuItem('entire column')
        item.connect('activate', copy_column_to_clipboard, treeview, col_index )
        copy_submenu.append(item)

        # get unique in column
        def get_unique_in_column(x,treeview,col_index):
          data = [str(row[col_index]) for row in treeview.get_model()]
          data = list(set(data))
          data.sort()
          for i in range(len(data)):
            if '"' in data[i]:
              data[i] = '"%s"' % (data[i].replace('"','\"'))
          s = ', '.join(data)
          dialog = gtk.MessageDialog(parent=None, flags=0, type=gtk.MESSAGE_INFO, buttons=gtk.BUTTONS_OK, message_format=None)
          dialog.set_markup('<b>%i unique value%s in column:</b>\n\n'%(len(data),pluralize(len(data)))+ pango_escape(s) +'')
          dialog.connect('delete-event', lambda x,y,d: d.destroy(), dialog )
          dialog.connect('response', lambda x,y,d: d.destroy(), dialog )
          dialog.show_all()
        item = gtk.ImageMenuItem('unique values in column...')
        item.connect('activate', get_unique_in_column, treeview, col_index )
        calculate_submenu.append(item)

      # export as CSV
      def export_as_csv(x,treeview):
        s = treeview_to_csv(treeview,only_selected=False)
        dialog = gtk.FileChooserDialog(title='Export as CSV', parent=None, action=gtk.FILE_CHOOSER_ACTION_SAVE, buttons=(gtk.STOCK_CANCEL,gtk.RESPONSE_CANCEL,gtk.STOCK_SAVE,gtk.RESPONSE_OK), backend=None)
        dialog.set_current_name('untitled.csv')
        csv_filter = gtk.FileFilter()
        csv_filter.set_name("CSV Files")
        csv_filter.add_pattern("*.csv")
        dialog.add_filter(csv_filter)
        if dialog.run()==gtk.RESPONSE_OK:
          fn = dialog.get_filename()
          async_file_write(fn,s)
        dialog.destroy()
      item = gtk.ImageMenuItem('as CSV...')
      item.connect('activate', export_as_csv, treeview )
      export_submenu.append(item)

      # export as XLS
      def export_as_xls(x,treeview):
        s = treeview_to_csv(treeview,only_selected=False,delim='\t')
        dialog = gtk.FileChooserDialog(title='Export as XLS', parent=None, action=gtk.FILE_CHOOSER_ACTION_SAVE, buttons=(gtk.STOCK_CANCEL,gtk.RESPONSE_CANCEL,gtk.STOCK_SAVE,gtk.RESPONSE_OK), backend=None)
        dialog.set_current_name('untitled.xls')
        xls_filter = gtk.FileFilter()
        xls_filter.set_name("XLS Files")
        xls_filter.add_pattern("*.xls")
        dialog.add_filter(xls_filter)
        if dialog.run()==gtk.RESPONSE_OK:
          fn = dialog.get_filename()
          async_file_write(fn,s)
        dialog.destroy()
      item = gtk.ImageMenuItem('as XLS...')
      item.connect('activate', export_as_xls, treeview )
      export_submenu.append(item)

      # export as SQL
      def export_as_sql(x,treeview,table_name):
        s = treeview_to_insert(treeview,table_name,only_selected=False)
        dialog = gtk.FileChooserDialog(title='Export as SQL', parent=None, action=gtk.FILE_CHOOSER_ACTION_SAVE, buttons=(gtk.STOCK_CANCEL,gtk.RESPONSE_CANCEL,gtk.STOCK_SAVE,gtk.RESPONSE_OK), backend=None)
        dialog.set_current_name('untitled.sql')
        sql_filter = gtk.FileFilter()
        sql_filter.set_name("SQL Files")
        sql_filter.add_pattern("*.sql")
        dialog.add_filter(sql_filter)
        if dialog.run()==gtk.RESPONSE_OK:
          fn = dialog.get_filename()
          async_file_write(fn,s)
        dialog.destroy()
      item = gtk.ImageMenuItem('as SQL...')
      item.connect('activate', export_as_sql, treeview, seen_tables[0][0]+self.conn.schema_tbl_sep+seen_tables[0][1] if seen_tables[0][0] else self.conn.schema_tbl_sep+seen_tables[0][1] )
      export_submenu.append(item)


      menu.show_all()
      menu.popup( None, None, None, event.button, event.time)
      return True    
